<?php

 
Zend_Loader::loadClass("BaseDBRows");
Zend_Loader::loadClass("StaffTraining");
class StaffTrainings extends BaseDBRows
{
	protected function setUp()
	{
		$this->_tableName = StaffTraining::_tablename;
	}
	
	public function getStaffTrainings($orgId, $staffId) {
	
		Zend_Loader::loadClass("Training");
		Zend_Loader::loadClass("Staff");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("st.*, t.course_title");
		$query->addFrom($this->_tableName . " st ");
		$query->addJoin("," . Training::_tablename . " t ");
		$query->addJoin("," . Staff::_tablename . " s ");
		$query->addWhere("st.training_id = t.training_id");
		$query->addWhere("st.staff_id = s.staff_id");
		$query->addWhere("s.org_id = " . $orgId);
		
		// staff
		if(strlen($staffId) > 0) {
			$query->addWhere("s.staff_id = " . $staffId);
		}
		
		$db = DBCon::instance();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function getTrainingsByStaff($staffId) {
	
		Zend_Loader::loadClass("Training");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("st.staff_training_id, t.course_title, st.date_actual_trained, st.actual_trainor, st.remarks");
		$query->addFrom($this->_tableName . " st ");
		$query->addJoin(", " . Training::_tablename . " t ");
		$query->addWhere("st.training_id = t.training_id");
		$query->addWhere("ifnull(actual_trainor,'') <> ''");
		$query->addWhere("ifnull(date_actual_trained,'') <> ''");
		$query->addWhere("staff_id = " . $staffId);
		$query->addOrderBy("st.date_actual_trained desc");
		
		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function getTrainingScheduleByStaff($staffId) {
	
		Zend_Loader::loadClass("Training");
		Zend_Loader::loadClass("Skill");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("st.staff_training_id, t.course_title, s.skill, st.date_default_trained, st.default_trainor, st.remarks");
		$query->addFrom($this->_tableName . " st ");
		$query->addJoin(", " . Training::_tablename . " t ");
		$query->addJoin(", " . Skill::_tablename . " s ");
		$query->addWhere("st.training_id = t.training_id");
		$query->addWhere("t.skill_id = s.skill_id");
		$query->addWhere("ifnull(actual_trainor,'') = ''");
		$query->addWhere("ifnull(date_actual_trained,'') = ''");
		$query->addWhere("staff_id = " . $staffId);	
		$query->addOrderBy("st.date_default_trained");
		
		$db = DBCon::instance();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function getStaffTrainingByTraining($orgId,$staffTrainingId) {
	
		Zend_Loader::loadClass("Training");
		Zend_Loader::loadClass("Skill");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("st.*, t.*, s.skill");
		$query->addFrom($this->_tableName . " st ");
		$query->addJoin(", " . Training::_tablename . " t ");
		$query->addJoin(" left join " . Skill::_tablename . " s on t.skill_id = s.skill_id ");
		$query->addWhere("st.training_id = t.training_id");
		$query->addWhere("t.org_id = " . $orgId);
		$query->addWhere("st.staff_training_id = " . $staffTrainingId);
		
		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function getStaffTrainingListByOrg($orgId,$staffId) {
	
		Zend_Loader::loadClass("Training");
		Zend_Loader::loadClass("Skill");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("t.*, s.skill, ifnull(st.staff_training_id,0) staff_training_id, if(cancel,'Cancelled',if(ifnull(staff_training_id,0)=0,'Not in Schedule',if(ifnull(st.date_actual_trained,'')<>'','Done','Waiting Schedule'))) training_status");
		$query->addFrom(Training::_tablename . " t ");
		$query->addJoin("left join (select * from " . $this->_tableName . " where staff_id = " . $staffId . ") st on t.training_id = st.training_id ");
		$query->addJoin("left join " . Skill::_tablename . " s on t.skill_id = s.skill_id ");
		$query->addWhere("t.org_id = " . $orgId);
		$query->addOrderBy("date_training desc");

		$db = DBCon::instance();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
}
?>